﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using ReflectionStudio.Core.Helpers;
using System.IO;
using ReflectionStudio.Core;
namespace WPFExcelReport
{
    public partial class JLTPLANData
    {
        public DataRow GetProcessFlow(string stage, string art, string var, string color, string matchid)
        {
            DataRow row = null;
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            string expression = "";
            expression = "STAGE='" + stage + "' AND MATCHID='" + matchid + "'";
            DataRow[] rows = SetDispo.Tables["DATADISPO"].Select(expression);
            if (rows.Length > 0)
            {
                row = rows[0];
            }
            return row;
        }
        public DataSet GetAPZUORD(string APZART, string APZVAR = "   ", string APZSTUFE = "F")
        {
            DataSet setReturn = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            OracleCommand or1;
            try
            {
                APZVAR = Left(APZVAR + "   ", 3);
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM APZUORD WHERE APZART=:APZART AND APZVAR=:APZVAR AND APZSTUFE=:APZSTUFE";
                or1.Parameters.Add("APZART", OracleType.NVarChar).Value = APZART;
                or1.Parameters.Add("APZVAR", OracleType.NVarChar).Value = APZVAR;
                or1.Parameters.Add("APZSTUFE", OracleType.NVarChar).Value = APZSTUFE;
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "APZUORD";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryWASTAMM(string art, string var)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM WASTAMM WHERE  "
                + "  WAMANDANT=:WAMANDANT AND WAART=:WAART AND WAVAR=:WAVAR";
                or1.Parameters.Add("WAMANDANT", OracleType.NVarChar).Value =Mandant;
                or1.Parameters.Add("WAART", OracleType.NVarChar).Value = art;
                or1.Parameters.Add("WAVAR", OracleType.NVarChar).Value = var;

                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "WASTAMM";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryWAFARBEN(string art, string var,string atype, string additive)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM WAFARBEN WHERE   "
            + "  WAFMANDANT=:WAFMANDANT AND WAFART=:WAFART AND WAFVAR=:WAFVAR AND WAFZUART=:WAFZUART AND WAFZUSATZ=:WAFZUSATZ ";
            or1.Parameters.Add("WAFMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("WAFART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("WAFVAR", OracleType.NVarChar).Value = var;
            or1.Parameters.Add("WAFZUART", OracleType.NVarChar).Value = atype;
            or1.Parameters.Add("WAFZUSATZ", OracleType.NVarChar).Value = additive;

            setResult = Connect2Proxy(or1);

            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "WAFARBEN";
            }
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryWASTKE(string art, string var, string color)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM WASTKE"
           + " WHERE WAKMANDANT=:WAKMANDANT AND WAKART=:WAKART AND WAKVAR=:WAKVAR AND WAKWCOL=:WAKWCOL";
            or1.Parameters.Add("WAKMANDANT", OracleType.NVarChar).Value =Mandant;
            or1.Parameters.Add("WAKART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("WAKVAR", OracleType.NVarChar).Value = var;
            or1.Parameters.Add("WAKWCOL", OracleType.NVarChar).Value = color;

            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "WASTKE";
            }
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryWASTGA(string art, string var, string color)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM WASTGA WHERE  "
            + "  WAGMANDANT=:WAGMANDANT AND WAGART=:WAGART AND WAGVAR=:WAGVAR AND WAGWCOL=:WAGWCOL AND WAGPOS='00'";
            or1.Parameters.Add("WAGMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("WAGART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("WAGVAR", OracleType.NVarChar).Value = var;
            or1.Parameters.Add("WAGWCOL", OracleType.NVarChar).Value = color; 
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "WASTGA";
            } 
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryBSSATZ(string art)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM  BSSATZ"
           + " WHERE "
            + " BSMANDANT=:BSMANDANT AND BSKART=:BSKART";
            or1.Parameters.Add("BSMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("BSKART", OracleType.NVarChar).Value = art;
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "BSSATZ";
            }
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryBSGROE(string art)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM  BSGROE"
           + " WHERE "
            + " BSGMANDANT=:BSGMANDANT AND BSGART=:BSGART";
            or1.Parameters.Add("BSGMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("BSGART", OracleType.NVarChar).Value = art;
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "BSGROE";

            }
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryBHSATZ(string art,string var)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM BHSATZ"
           + " WHERE BHMANDANT=:BHMANDANT AND BHKART=:BHKART AND BHKVAR=:BHKVAR";
            or1.Parameters.Add("BHMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("BHKART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("BHKVAR", OracleType.NVarChar).Value = var;
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "BHSATZ";
            }
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryBASTGA(string art, string var, string color)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM BASTGA WHERE  "
            + "  BAGMANDANT=:BAGMANDANT AND BAGART=:BAGART AND BAGVAR=:BAGVAR AND BAGFARBE=:BAGFARBE";
            or1.Parameters.Add("BAGMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("BAGART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("BAGVAR", OracleType.NVarChar).Value = var;
            or1.Parameters.Add("BAGFARBE", OracleType.NVarChar).Value = color;
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "BASTGA";
            }
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryGSDAT(string art, string var)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1; 
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GSDAT WHERE  "
            + "  GSMANDANT=:GSMANDANT AND GSART=:GSART AND GSVAR=:GSVAR";
            or1.Parameters.Add("GSMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("GSART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("GSVAR", OracleType.NVarChar).Value = var; 
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "GSDAT";
            }
            setReturn.Merge(setResult);
            return setReturn;
        }
        public DataSet QueryGASTGA(string art, string var,string color)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GASTGA WHERE  "
            + "  GAGMANDANT=:GAGMANDANT AND GAGART=:GAGART AND GAGVAR=:GAGVAR AND GAGFARBE=:GAGFARBE";
            or1.Parameters.Add("GAGMANDANT", OracleType.NVarChar).Value = Mandant;
            or1.Parameters.Add("GAGART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("GAGVAR", OracleType.NVarChar).Value = var;
            or1.Parameters.Add("GAGFARBE", OracleType.NVarChar).Value = color;
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                tbLocal.TableName = "GASTGA";
            }
            setReturn.Merge(setResult);
            return setReturn;
        }
          
        public DataSet QueryVKWDECK(string krs,string lfd,string pos)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            OracleCommand or1;
            try
            { 
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM WDECK "
                + " WHERE WDMANDANT=:WDMANDANT AND WDSA='K' AND WDUPOS='00' "
                + " AND WDAUFKRS=:WDAUFKRS AND WDAUFLFD=:WDAUFLFD AND WDAUFPOS=:WDAUFPOS";
                or1.Parameters.Add("WDMANDANT", OracleType.NVarChar).Value = Mandant;
                or1.Parameters.Add("WDAUFKRS", OracleType.NVarChar).Value = krs;
                or1.Parameters.Add("WDAUFLFD", OracleType.NVarChar).Value = lfd;
                or1.Parameters.Add("WDAUFPOS", OracleType.NVarChar).Value = pos; 
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = "WDECK";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryVKKOPF(string KRS, string LFD)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet(); 
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM VKKOPF WHERE "
                                                + "VKKAUFKRS=:VKKAUFKRS AND VKKAUFLFD=:VKKAUFLFD";
                or1.Parameters.Add("VKKAUFKRS", OracleType.NVarChar).Value = KRS;
                or1.Parameters.Add("VKKAUFLFD", OracleType.NVarChar).Value = LFD;
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "VKKOPF";
                }
                setReturn.Merge(setResult);
                setResult = LoadVKPOS(KRS, LFD);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryVKPOS( string krs, string lfd, string pos)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            OracleCommand or1;
            try
            {
                setResult = LoadVKPOS(krs, lfd, pos);
                setReturn.Merge(setResult); 

                setResult=LoadAB17(krs,lfd,pos);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet LoadVKPOS(string KRS, string LFD)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT *"
                                          + " FROM INTEX.VKPOS "
                                          + " WHERE VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD "
                                          + " AND VKPSA<>'AJ' AND VKPAUFART ='NA' ORDER BY VKPAUFPOS";
                or1.Parameters.Add("VKPAUFKRS", KRS);
                or1.Parameters.Add("VKPAUFLFD", LFD);
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "VKPOS";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet LoadVKPOS(string KRS, string LFD, string POS)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT *"
                                          + " FROM INTEX.VKPOS "
                                          + " WHERE VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD AND VKPAUFPOS=:VKPAUFPOS"
                                          + " AND VKPSA<>'AJ' AND VKPAUFART ='NA'";
                or1.Parameters.Add("VKPAUFKRS", KRS);
                or1.Parameters.Add("VKPAUFLFD", LFD);
                or1.Parameters.Add("VKPAUFPOS", POS);
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "VKPOS"; 
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        } 
        public DataSet LoadSetDispo(string setID = "")
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\Interface\PRODSETUP.XML");
                if (!System.IO.File.Exists(filePath))
                {
                    return setReturn;
                }
                setResult.ReadXml(filePath);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public void SaveSetDispo(DataTable tbDispo)
        {
            DataSet setDispo = new DataSet();  
            try
            {
                setDispo.Merge(tbDispo);
                string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\Interface\DATADISPO.XML");
                if (System.IO.File.Exists(filePath))
                {
                    System.IO.File.Delete(filePath);
                    setDispo.WriteXml(filePath);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        } 
        public DataSet LoadAB17(string krs,string lfd,string pos)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbWDECK = null;
            DataTable tbWASTKE = null;
            DataSet setTemp;
            try
            {
                setResult = QueryVKWDECK(krs,lfd,pos); 
                tbWDECK = setResult.Tables["WDECK"];
                if(tbWDECK.Rows.Count==0)
                    return setResult;
                setReturn.Merge(setResult);   
                DataRow dwWDECK = tbWDECK.Rows[0];
                string atype = dwWDECK["WDWAKZ"].ToString();
                string vkart = dwWDECK["WDART"].ToString();
                string vkvar = dwWDECK["WDVAR"].ToString();
                string vkcolor= dwWDECK["WDFARBE"].ToString();
                string vkfinish = dwWDECK["WDBESCHICH"].ToString();
                setResult = QueryWASTAMM(vkart, vkvar);
                setReturn.Merge(setResult); 
                setResult = QueryWASTKE(vkart, vkvar, vkcolor);
                setReturn.Merge(setResult);
                setResult = QueryWAFARBEN(vkart, vkvar,"C", vkcolor);
                setReturn.Merge(setResult);
                setResult = QueryWAFARBEN(vkart, vkvar, "B", vkfinish);
                setReturn.Merge(setResult);                
                setResult = QueryWASTGA(vkart, vkvar, vkcolor);
                setReturn.Merge(setResult);
                 tbWASTKE =setReturn.Tables["WASTKE"];
                 foreach (DataRow row in tbWASTKE.Rows)
                {
                     // WAKKART WAKKVAR WAKKFARBE
                    string bart = row["WAKKART"].ToString();
                    string bvar = row["WAKKVAR"].ToString();
                    string bcolor = row["WAKKFARBE"].ToString();
                    setTemp = QueryBSSATZ(bart);
                    setReturn.Merge(setTemp);
                    setTemp = QueryBSGROE(bart);
                    setReturn.Merge(setTemp);
                    setTemp = QueryBHSATZ(bart,bvar);
                    setReturn.Merge(setTemp);                    
                    setTemp = QueryBASTGA(bart, bvar, bcolor);
                    setReturn.Merge(setTemp);  
                } 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}
